package com.pay.utils;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbutils.BaseResultSetHandler;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * 数据库连接
 * Created by Administrator on 2017-7-5.
 */
public class DatabaseHelper {

    private static final Logger logger = LoggerFactory.getLogger(DatabaseHelper.class);
    private static ThreadLocal<Connection> CONNECTION_HOLDER;
    private static QueryRunner QUERY_RUNNER;
    private static BasicDataSource DATA_SOURCE;

    static {
        CONNECTION_HOLDER = new ThreadLocal<Connection>();
        QUERY_RUNNER = new QueryRunner();

        PropertiesLoader loader = new PropertiesLoader(Constants.CONFIG_FILE);
        Properties properties = loader.getProperties();

        DATA_SOURCE = new BasicDataSource();
        DATA_SOURCE.setDriverClassName(properties.getProperty(Constants.DB_DRIVER));
        DATA_SOURCE.setUrl(properties.getProperty(Constants.DB_URL));
        DATA_SOURCE.setUsername(properties.getProperty(Constants.DB_USERNAME));
        DATA_SOURCE.setPassword(properties.getProperty(Constants.DB_PASSWORD));
    }

    /**
     * 获取数据库连接
     * @return
     */
    public static Connection getConnection(){
        Connection connection = CONNECTION_HOLDER.get();
        if(null == connection) {
            try {
                //connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
                connection = DATA_SOURCE.getConnection();
            } catch (Exception ex) {
                logger.error("can not create datasource connection!");
                ex.printStackTrace();
            }finally {
                CONNECTION_HOLDER.set(connection);
            }
        }
        return connection;
    }

    /**
     * 关闭数据库连接
     *
     */
    public static void closeConnection(){
        Connection connection = CONNECTION_HOLDER.get();
        if(null != connection) {
            try {
                connection.close();
            } catch (Exception ex) {
                logger.error("can not close datasource connection!");
                ex.printStackTrace();
            }finally {
                CONNECTION_HOLDER.remove();
            }
        }
    }

    /**
     * 返回单实体列表查询
     * @Param
     * @return
     */
    public static <T> List<T> queryEntityList(Class<T> entityClass, String sql, Object... params){
        List<T> entityList = new ArrayList<T>();
        try {
            Connection conn = getConnection();
            entityList = QUERY_RUNNER.query(conn,sql,new BeanListHandler<T>(entityClass),params);
        }catch (Exception ex){
            logger.error("query entityList error!");
            ex.printStackTrace();
        }finally {
            closeConnection();
        }
        return entityList;
    }

    /**
     * 返回单实体查询
     * @param entityClass
     * @param sql
     * @param params
     * @param <T>
     * @return
     */
    public static <T> T queryEntity(Class<T> entityClass, String sql, Object... params){
        T entity = null;
        try {
            Connection conn = getConnection();
            entity = QUERY_RUNNER.query(conn,sql,new BeanHandler<T>(entityClass),params);
        }catch (Exception ex){
            logger.error("query entity error!");
            ex.printStackTrace();
        }finally {
            closeConnection();
        }
        return entity;
    }

    /**
     * 返回列与列值得列表，适用于多表查询
     * @param sql
     * @param params
     * @return
     */
    public static List<Map<String,Object>> executeQuery(String sql,Object... params){
        List<Map<String,Object>> result = new ArrayList<Map<String,Object>>();
        try {
            Connection conn = getConnection();
            result = QUERY_RUNNER.query(conn, sql, new MapListHandler(), params);
        }catch (Exception ex){
            logger.error("query entity error!");
            ex.printStackTrace();
        }finally {
            closeConnection();
        }
        return result;
    }

    /**
     * 执行更新语句
     * @param sql
     * @param params
     * @return
     */
    public static int executeUpdate(String sql,Object... params){
        int rows = 0;
        try {
            Connection conn = getConnection();
            rows = QUERY_RUNNER.update(conn,sql,params);
        }catch (Exception ex){
            logger.error("query entity error!");
            ex.printStackTrace();
        }finally {
            closeConnection();
        }
        return rows;
    }

    /**
     * 插入实体
     * @param entityClass
     * @param fieldMap
     * @param <T>
     * @return
     */
    public static <T> boolean insertEntity(Class<T> entityClass,Map<String,Object> fieldMap){
        if(CollectionUtils.isEmpty(fieldMap)){
            logger.error("can not insert entity:fieldMap is empty");
            return false;
        }

        String sql = "insert into " + getTableName(entityClass);
        StringBuilder colums = new StringBuilder("(");
        StringBuilder values = new StringBuilder("(");
        for(String fieldName:fieldMap.keySet()){
            colums.append(fieldName).append(",");
            values.append("?,");
        }
        colums.replace(colums.lastIndexOf(","),colums.length(),")");
        values.replace(values.lastIndexOf(","),values.length(),")");

        sql += colums + "values " + values;
        Object[] params = fieldMap.values().toArray();
        return executeUpdate(sql,params) == 1;
    }

    /**
     * 更新实体
     * @param entityClass
     * @param id
     * @param fieldMap
     * @param <T>
     * @return
     */
    public static <T> boolean updateEntity(Class<T> entityClass,long id,Map<String,Object> fieldMap){
        if(CollectionUtils.isEmpty(fieldMap)){
            logger.error("can not insert entity:fieldMap is empty");
            return false;
        }
        String sql = "update " + getTableName(entityClass) + " set ";
        StringBuilder colums = new StringBuilder();
        for(String fieldName:fieldMap.keySet()){
            colums.append(fieldName).append("=?,");
        }
        sql += colums.substring(0,colums.lastIndexOf(",")) + "where id = ?";

        List<Object> paramList = new ArrayList<Object>();
        paramList.addAll(fieldMap.values());
        paramList.add(id);

        Object[] params = paramList.toArray();

        return executeUpdate(sql,params) == 1;
    }

    /**
     * 删除实体
     * @param entityClass
     * @param id
     * @param <T>
     * @return
     */
    public static <T> boolean deleteEntity(Class<T> entityClass,long id){
        String sql = "delete from "+ getTableName(entityClass) + " where id=?";
        return executeUpdate(sql,id) == 1;
    }

    private static String getTableName(Class<?> entityClass){
        return entityClass.getSimpleName();
    }

    /**
     * 执行SQL脚本文件
     * @param path
     */
    public static void executeSQLFile(String path){
        InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(path);
        BufferedReader br = new BufferedReader(new InputStreamReader(is));
        try {
            String sql = "";
            while ((sql = br.readLine()) != null){
                executeUpdate(sql);
            }
        }catch (Exception ex){
            logger.error("execute sql file failed!",ex);
            throw new RuntimeException(ex);
        }


    }
}
